import json
import requests
import pymysql

#智慧海事-业务平台-动态执法-任务跟踪-巡航检查任务
# 发送目标请求
BASE_URL = "http://198.10.51.2:8090/task/taskCooperation/getTaskCollaborationTable?flag=0&pendingUnitOrg=01020214&flag1=true&crusingStartTime=2024-10-01&crusingEndTime=2024-12-31&checkObjectCategory=5&taskTypeCode=0&pageNo=1&status=0&pageSize=10000&depOrg=01020214010205&orgId=1745010892529930321&jsDepOrg=01020214010205&jsOrgName=%E4%BA%BA%E4%BA%8B%E6%95%99%E8%82%B2%E5%A4%84&userId=1746180604009803778"
headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer eyJhbGciOiJIUzUxMiJ9.eyJ1c2VySWQiOjE3NDYxODA2MDQwMDk4MDM3NzgsImFjY291bnQiOiJrWkVROXMzcitrNmRibENCRVZ3by9lUUxPRDVUR1hFVFlLMmtIQ3FoSzhJPSIsInV1aWQiOiIyMWVmZjY2Yi00ZTA5LTQyMWItYTJjMy0yZmIxZTY0NTdhNGEiLCJzdWIiOiIxNzQ2MTgwNjA0MDA5ODAzNzc4IiwiaWF0IjoxNzQwODgxNjc2LCJleHAiOjE3NDA5NjgwNzZ9.wUun9QL1Cwu5hah5XRQaOiQ81TBYIbOFrHl5YxcTsnsxVrmXgVwS43Ecbfgi_Lbzvw-LPMCYRBhhEgJI5pqWPw"
}

cookies = {
}

data = {

}

#组装请求配置
session = requests.Session()
session.headers.update(headers)


#请求指标11船舶污染物的接收和处理情况备案数量
response_lo = session.get(BASE_URL, cookies=cookies)

#响应回来的参数
#print("响应回来的参数:",response_lo.text)
#响应回来的参数文本转json
strData = json.loads(response_lo.text)
strRecordsData = strData['data']['rows'] #获取到具体数据的位置

#获取数据情况records、total、size、current、pages
print("获取："+str(strData['data']['totalRows'])+"条数据","获取："+str(strData['data']['totalPage'])+"页数据","获取每页设置"+str(strData['data']['pageSize'])+"条数据")

#打印每一列数据的字段key
#print("获取建表字段：" , tuple(strRecordsData[0].keys())  )

if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'ywtg_xunhangjiancha_27_29'

    # 连接数据库
    connection = pymysql.connect(**db_config)

try:
    # 开始事务
    with connection.cursor() as cursor:
        # 循环插入每条记录
        for record in strRecordsData:
            # 获取需要插入的字段名称
            fields = ', '.join(record.keys())
            # 判断是否包含 "index"，如果包含则替换为 "index1"
            if "index" in fields:
                fields = fields.replace("index", "index1")
            # 拼装需要插入字段对应的%号个数
            values = ", ".join(['%s'] * len(record))

            # 构建插入语句，插入业务主表
            insert_stmt = (
                "INSERT INTO `{table}` ({fields}) "
                "VALUES ({values})"
            ).format(
                table=table_name,
                fields=fields,
                values=values
            )
            # 插入语句
            #print("插入SQl语句：", insert_stmt)
            #print("插入值：", tuple(record.values()))
            # 执行sql语句插入数据，record.values()用于获取字段中的值
            cursor.execute(insert_stmt, tuple(record.values()))

    # 提交事务
    connection.commit()
finally:
    # 关闭数据库连接
    connection.close()



